Database Management Systems (DBMS) such as Microsoft.RTM. SQL Server, provide for storing and managing information. The majority of applications that need to access data stored in such a system go through a data access layer such as the Microsoft.RTM. Open Database Connectivity (ODBC) layer or the Microsoft.RTM. OLE database (OLEDB). These layers provide many services that make writing applications that need access to information stored in databases much easier. These services include running database queries and fetching result sets without having to know the underlying protocols need to communicate with the DBMS.
Most data access layers provide a way to pass database commands directly to the underlying DBMS. These commands are captured in a database language that is understood by the DBMS or can be readily translated using a DBMS driver. For example, ODBC uses SQL (Server Query Language) as the language for issuing commands to the DBMS. Database query languages are comprehensive and cover operations from fetching simple data sets to backing/restoring of databases. Most database applications, however, use only a small subset of the database query language, specifically commands that fetch and update the data sets.
Writing applications that rely heavily on reading and writing information to a database back-end is not trivial. In such applications, consideration must be given to efficiently retrieving data from the DBMS, or at least efficiently transferring requests and data between the front-end application and the DBMS. For example, in a web site application it is desirable for reasons of efficiency and speed to keep to a minimum the number of round trips between the web server and a server maintaining the DBMS. Another challenge in implementing a DBMS accessed through a front-end application is maintenance of the front-end code. Such code must accommodate changes in the underlying organization or schema of the DBMS tables, columns and constraints, and changes to the data sought by the front-end application for display or delivery to a user. It is undesirable to rewrite front-end code each time one of such changes is needed.
An example of a DBMS accessed through a front-end application is a web site that provides customers with an interface to browse and buy books online, wherein information about the books is stored in a SQL server database. When a user asks to display information about a book the web server retrieves the information from the database, renders it using HTML, and sends it back to the user. This effectively means that just about every user request will result in a database query. Also, changing and improving such a web site usually means that the queries going to the SQL server have to change, resulting in code maintenance. For example, if a display of a book's ISBN number is added to the web site, the query to the database must be changed to get the ISBN number in addition to the other information that had previously been retrieved. This may also mean that the underlying schema of the database might change. For example, if the ISBN number was not previously stored in the database it would have to be added, requiring that the underlying schema of the database might change. This change in schema may require that any existing code for executing requests be updated or changed. Obviously, it is desirable if rewriting of the code required to query a DBMS can be kept to a minimum.
Therefore, there is a need for system which can automatically generate queries based on a high level specification of the data required by the front-end application and from a high level description of the schema of the back end DBMS.